Spreadsheets are ubiquitous in modern business and almost all businesses rely on them for at least some of their business processes. Spreadsheets have become so popular primarily due to their accessibility and flexibility—users with specific domain knowledge can quickly learn to build powerful business applications without requiring the input of IT professionals. These user-created spreadsheets can in many cases be regarded as “user-defined software applications”—i.e. applications that have been created without the participation of professional software developers.
The accessibility and ease-of-use of spreadsheets comes at a price, however: spreadsheets are usually uncontrolled within the organisation and are highly prone to errors. The causes of such errors are numerous, and include lack of control over versioning, multiple versions of the same file being used and the mixing of models and data in a single file.
An aim of our disclosure is to put the use of spreadsheets onto a more robust and rigorous footing, while at the same time ensuring that the resulting system can be exploited by users who do not have any software development skills. The resulting spreadsheet-based systems can be built by users who need only be skilled in the use of spreadsheets, not in the development of computer software.
In typical usage, what is commonly referred to as a “spreadsheet” is in fact a file (or a “workbook”) that is populated by one or more spreadsheet worksheets. The spreadsheet model and associated data will normally be distributed across multiple worksheets, and a workbook will often contain more than one model. Spreadsheet workbooks are commonly stored on disk as a file, either on the user's desktop computer or mobile device, or on a network shared folder. In some cases, spreadsheet workbooks are stored within a database as a binary object. A spreadsheet workbook can be regarded as a container that holds spreadsheet objects (or modules) such as worksheets, charts and scripts and manages the interaction of these objects with the spreadsheet engine.
The single-file architecture of spreadsheet workbooks means that models and data are often duplicated, usually combined in the same file, and will often be out-of-date. In an effort to promote model re-use, and to help keep data separate from models, many users exploit a feature of modern spreadsheet software (e.g. Microsoft Excel, OpenOffice Calc, LibreOffice Calc, to name a few examples) called “linked workbooks”. In this configuration, the name and location of the spreadsheet workbook containing the data is stored within the workbook containing the model. When the model workbook is opened the spreadsheet software attempts to open any referenced linked data workbooks.
In practice, the use of linked workbooks can be problematic: Users can re-name linked workbooks and they can be moved to a different location, or even deleted. When a linked workbook is stored on a network then additional problems can arise. This is particularly true for mobile networks where connections can be unreliable and data transmission rates can be low.
Another major problem with the standard file-based spreadsheet implementation is that opportunities for model re-use are very low, even with linked workbooks. Users will often end up “re-inventing the wheel” or having multiple copies of spreadsheets that have very small differences in their design. To compound the problems, without version control users are often unsure which model is the “correct” one to use.
A typical spreadsheet workbook with a typical calculation model will comprise one or more worksheets with some combination of the following:                Formulas in worksheet cells for performing calculations        Static data used in calculations        Dynamic data used in calculations (e.g. retrieved from a web service)        User-supplied input values        Cell formatting information        “Reporting” objects such as charts and pivot tables        (Optionally) user-defined functions created using a scripting language        
With all of these objects combined into a single file, re-use is difficult to achieve. User-defined functions can be consolidated into a single workbook which is then linked-to from the model workbook. However, the same problems with linked workbooks that are encountered with external data workbooks also apply here.
Another method for improving the management of spreadsheet models is the creation of so-called Spreadsheet Services. In a Spreadsheet Service the spreadsheet engine is normally located on a computer server, which is in turn located on a computer network. This network can be either a LAN or a WAN. In the service model the user initiates a service request from their device and the request parameters are dispatched to the remote service. Upon arrival at the service provider device, the parameters are applied to the spreadsheet associated with the service, the spreadsheet is recalculated and the resulting output values are returned to the user's device. This is a standard “web service” model—it just so happens that the data processing is carried out by a spreadsheet.
By locating the service on a central server, complete control can be exercised over the model and data. The obvious disadvantage of this approach is that it removes the creation and deployment of the spreadsheet from the domain, or expert, user and puts it in the hands of the software professional. In addition, it does nothing to solve the problem of model re-use since it is still based on the single-file approach.
Module-based software development (usually combined with interface information in the form of associated metadata) is well known to practitioners. Systems also have been described in the patent literature that apply these concepts to spreadsheets and involve models and module documents, associated information describing the inputs and outputs, and information on how modules are connected together. These proposals, however, are still based on a document-centric approach to spreadsheet design and management.
Given the problems described above with existing spreadsheet implementations, it is clear that a need exists to provide a system and methods that give a user the capability to build robust and re-usable spreadsheets that can be more easily managed and controlled. Such a system must provide a user experience that is as close to the standard spreadsheet application as possible, if the advantages of spreadsheets for the user are not to be lost.
These teachings describe an approach to spreadsheets that represents a move away from a document-centric approach to one focused on functional entities, metadata and data. Spreadsheets have traditionally been treated in the same manner as “dumb” documents such as word processing documents (e.g. those created using Microsoft Word) and presentation documents (e.g. those created using Microsoft Powerpoint). Management of spreadsheets has consequently been carried out using standard document management systems.
By treating spreadsheets as composites of functional, style and data entities we realise a number of significant advantages that would not be possible in the document-centric approach. In particular, the system provides a means of achieving code/module reuse and creates a framework for building robust User Defined Applications (UDA) based on spreadsheet models.